﻿$fileName = (Get-Date).ToString("yyyy-MM-dd")
$Users = "SBP_"+$fileName + "_"+"Users"
$ADusers = $fileName + "_" + "ADusers"

$loggInDate2 = (Get-Date).AddDays(1).ToString('yyyy-MM-dd 00:00:00')
$loggInDate1 = (Get-Date).AddDays(-7).ToString('yyyy-MM-dd 00:00:00')

function get-DataSQL ($paramServer, $paramDatabase,$paramSQL, $paramFileName) {
    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
    $SqlConnection.ConnectionString = "Server = $paramServer; database = $paramDatabase; Integrated Security=True"
    
    $sqlCommandAll = New-Object System.Data.SqlClient.SqlCommand
    $sqlCommandAll.CommandText = $paramSQL
    $sqlCommandAll.Connection = $SqlConnection

    $sqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
    $sqlAdapter.SelectCommand = $sqlCommandAll 

    $sqlDataSet = New-Object System.Data.DataSet
    $sqlAdapter.Fill($sqlDataSet)

    $sqlDataSet.Tables[0] | Export-Csv "C:\Users\wa-AIvLatysheva\Statistics_Service\$paramFileName.csv" -Delimiter "," -Encoding Unicode

}

#___________________________________________________________________________________#

$server = "SPB99-KPDM-DB1"
$database = "NS_KASKAD_PDM_prod"
$sql = "
select id, userName, lockedUntil, lastActivity, creationDate,
	(
	select count(eventType)
	from audit
	where 
		audit.eventType = 300 and 
		audit.timestamp between '$loggInDate1' and '$loggInDate2' and
		audit.principalId = ns_sec_Users.id
	) as countOfLogInFor_Week,
	(
	select ns_sec_Roles.name
	from ns_sec_UserRoles
	inner join ns_sec_Roles on ns_sec_UserRoles.roleId = ns_sec_Roles.id
	where 
		ns_sec_UserRoles.userId = ns_sec_Users.id and
		ns_sec_Roles.name = 'block'
	) as roleBlock,
	(
		select count(eventType)
		from audit
		where 
			audit.eventType = 300 and 
			audit.timestamp > '2022-01-01 00:00:00' and
			audit.principalId = ns_sec_Users.id
	) as countOfLogInFor_Year
from ns_sec_Users
where ns_sec_Users.userName != 'Latysheva.AIv' and
	ns_sec_Users.userName != 'Baranov.RAn' and
	ns_sec_Users.userName != 'Grishchenko.AA' and
	ns_sec_Users.userName != 'Dundukova.PM' and
	ns_sec_Users.userName != 'Svetlichnyy.SD' and
	ns_sec_Users.userName != 'RAnBaranov'and 
	ns_sec_Users.userName != 'Borisova.EYu' and
	ns_sec_Users.userName != 'khamadiyarova.ve' and
	ns_sec_Users.userName != 'Penyushin.MD'
"

#___________________________________________________________________________________#

Get-ADGroupMember -Identity 'SPB-GPN-BRD-KPDM-Users' | foreach { Get-ADUser $_ -Properties Company }| Export-Csv "C:\Users\wa-AIvLatysheva\Statistics_Service\$ADusers.csv" -Delimiter "," -Encoding Unicode

get-DataSQL -paramServer $server -paramDatabase $database -paramSQL $sql -paramFileName $Users
